﻿Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.Odbc
Public Class PedidoDatos

    Dim Path1 As String = "H:\DATOS\CFG\"
    Dim Path2 As String = "H:\DATOS\PRUEBA\"

    Sub actualizarAprobacionPedido(cia As String, suc As String, tdoc As String, codPedido As String, estado As String, observaciones As String, mdpCodigo As String)
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "UPDATE Documento_Cliente " &
                              "SET DCI_Estado = @EST, DCI_ObsCredito = @OBS, MDP_Codigo = @MDP " &
                              "WHERE CIA = @CIA AND SUC_Codigo = @SUC AND TDOC_Codigo = @TDOC AND DCI_Codigo = @DCI"

        Dim comando As New SqlCommand
        With comando
            .Connection = coneccion
            .CommandText = query
            .Parameters.AddWithValue("@EST", estado)
            .Parameters.AddWithValue("@OBS", observaciones)
            .Parameters.AddWithValue("@MDP", IIf(mdpCodigo = "0", DBNull.Value, mdpCodigo))
            .Parameters.AddWithValue("@CIA", cia)
            .Parameters.AddWithValue("@SUC", suc)
            .Parameters.AddWithValue("@TDOC", tdoc)
            .Parameters.AddWithValue("@DCI", codPedido)
            .ExecuteNonQuery()
        End With
        coneccion.Close()
    End Sub

    Function obtenerTiposDocumentos(codDocumento As String, tipo As Integer) As DataSet
        Dim conex As New Conexion
        Dim coneccion As New SqlConnection
        Dim ds As New DataSet
        Dim query As String = ""
        Try
            coneccion = conex.conectar()
            coneccion.Open()

            If tipo = 1 Then
                query = "SELECT TDO_Codigo,TDO_Descripcion " &
                        "FROM TipoDocumento " &
                        "WHERE TDO_Codigo LIKE '" & codDocumento & "%' " &
                        "AND TDO_Venta = '1' AND TDO_Activo = '1'"
            ElseIf tipo = 2 Then
                query = "SELECT TDO_Codigo, UPPER(TDO_Abreviatura) AS Abreviatura " &
                        "FROM TipoDocumento " &
                        "WHERE TDO_Pedido = '1' AND TDO_Activo = '1' " &
                        "ORDER BY UPPER(TDO_Abreviatura) DESC"
            ElseIf tipo = 3 Then
                query = "SELECT TDO_Codigo,TDO_Descripcion " &
                        "FROM TipoDocumento " &
                        "WHERE TDO_Codigo LIKE '" & codDocumento & "%' " &
                        "AND TDO_Activo = '1'"
            End If

            Dim adapter As New SqlDataAdapter(query, coneccion)
            adapter.Fill(ds)
        Catch ex As SqlException
            MsgBox("La conexión no está disponible.")
        Catch e As Exception
            MsgBox(e.Message)
        Finally
            coneccion.Close()
        End Try

        Return ds
    End Function

    Function obtenerCondionesVenta(codCondicion As String, codCliente As String) As DataSet
        Dim conex As New Conexion
        Dim coneccion As New SqlConnection
        Dim ds As New DataSet
        Try
            coneccion = conex.conectar()
            coneccion.Open()

            Dim query As String = "SELECT cvc.CNV_Codigo, cv.CNV_Descripcion " &
                                  "FROM CondicionVentaCliente cvc " &
                                  "INNER JOIN CondicionVenta cv " &
                                  "ON cv.CNV_Codigo = cvc.CNV_Codigo AND cv.CNV_Activo = '1' " &
                                  "WHERE cvc.CLI_Codigo = '" & codCliente & "' AND cvc.CNC_Activo = '1' " &
                                  "AND cvc.CNV_Codigo LIKE '" & codCondicion & "%'"

            Dim adapter As New SqlDataAdapter(query, coneccion)
            adapter.Fill(ds)
        Catch ex As SqlException
            MsgBox("La conexión no está disponible.")
        Catch e As Exception
            MsgBox(e.Message)
        Finally
            coneccion.Close()
        End Try

        Return ds
    End Function

    Function obtenerFormaPago(codCondicion As String, codMoneda As String, codPago As String) As DataSet
        Dim conex As New Conexion
        Dim coneccion As New SqlConnection
        Dim ds As New DataSet
        Try
            coneccion = conex.conectar()
            coneccion.Open()

            Dim query As String = "SELECT fp.FOP_Codigo,fp.FOP_Descripcion,fpm.FPM_MontoMinimo,fp.FOP_PorcDsto " &
                                  "FROM FormaPago fp " &
                                  "INNER JOIN FormaPagoMoneda fpm " &
                                  "ON fpm.FOP_Codigo = fp.FOP_Codigo AND fpm.FPM_Activo = '1' AND fpm.MON_Codigo = '" & codMoneda & "' " &
                                  "WHERE fp.CNV_Codigo = '" & codCondicion & "' AND fp.FOP_Activo = '1' AND fp.FOP_Codigo LIKE '%" & codPago & "%'"

            Dim adapter As New SqlDataAdapter(query, coneccion)
            adapter.Fill(ds)
        Catch ex As SqlException
            MsgBox("La conexión no está disponible.")
        Catch e As Exception
            MsgBox(e.Message)
        Finally
            coneccion.Close()
        End Try

        Return ds
    End Function

    Function obtenerMonedas(codMoneda As String) As DataSet
        Dim conex As New Conexion
        Dim coneccion As New SqlConnection
        Dim ds As New DataSet
        Try
            coneccion = conex.conectar()
            coneccion.Open()

            Dim query As String = "SELECT MON_Codigo,MON_CodigoSIS,MON_Descripcion + ' (' + MON_Abreviatura + ')' AS Descripcion " &
                                  "FROM Moneda " &
                                  "WHERE MON_Codigo LIKE '" & codMoneda & "%' " &
                                  "AND MON_Activo = '1'"

            Dim adapter As New SqlDataAdapter(query, coneccion)
            adapter.Fill(ds)
        Catch ex As SqlException
            MsgBox("La conexión no está disponible.")
        Catch e As Exception
            MsgBox(e.Message)
        Finally
            coneccion.Close()
        End Try

        Return ds
    End Function

    Function obtenerListaPrecios(codLista As String) As DataSet
        Dim conex As New Conexion
        Dim coneccion As New SqlConnection
        Dim ds As New DataSet
        Try
            coneccion = conex.conectar()
            coneccion.Open()

            Dim query As String = "SELECT LIS_Codigo,LIS_Descripcion " &
                                  "FROM ListaPrecio " &
                                  "WHERE LIS_Codigo LIKE '" & codLista & "%' " &
                                  "AND LIS_Activo = '1'"

            Dim adapter As New SqlDataAdapter(query, coneccion)
            adapter.Fill(ds)
        Catch ex As SqlException
            MsgBox("La conexión no está disponible.")
        Catch e As Exception
            MsgBox(e.Message)
        Finally
            coneccion.Close()
        End Try

        Return ds
    End Function

    Function obtenerListaBancos(codBanco As String) As DataSet
        Dim conex As New Conexion
        Dim coneccion As New SqlConnection
        Dim ds As New DataSet
        Try
            coneccion = conex.conectar()
            coneccion.Open()

            Dim query As String = "SELECT BAN_Codigo,BAN_Descripcion " &
                                  "FROM Banco " &
                                  "WHERE BAN_Codigo LIKE '" & codBanco & "%' " &
                                  "AND BAN_Activo = '1' AND " &
                                  "BAN_Abreviatura <> ''"

            Dim adapter As New SqlDataAdapter(query, coneccion)
            adapter.Fill(ds)
        Catch ex As SqlException
            MsgBox("La conexión no está disponible.")
        Catch e As Exception
            MsgBox(e.Message)
        Finally
            coneccion.Close()
        End Try

        Return ds
    End Function

    Function obtenerListaVendedores(codVendedor As String) As DataSet
        Dim conex As New Conexion
        Dim coneccion As New SqlConnection
        Dim ds As New DataSet
        Try
            coneccion = conex.conectar()
            coneccion.Open()

            Dim query As String = "SELECT VEN_Codigo,VEN_Descripcion " &
                                  "FROM Vendedor " &
                                  "WHERE VEN_Codigo LIKE '%" & codVendedor & "%' " &
                                  "AND VEN_Activo = '1'"

            Dim adapter As New SqlDataAdapter(query, coneccion)
            adapter.Fill(ds)
        Catch ex As SqlException
            MsgBox("La conexión no está disponible.")
        Catch e As Exception
            MsgBox(e.Message)
        Finally
            coneccion.Close()
        End Try

        Return ds
    End Function

    Function obtenerListaCobradores(codCobrador As String, codCIA As String) As DataSet
        Dim conex As New Conexion
        Dim coneccion As New SqlConnection
        Dim ds As New DataSet
        Try
            coneccion = conex.conectar()
            coneccion.Open()

            Dim query As String = "SELECT COB_Codigo, COB_Descripcion " &
                                  "FROM Cobrador " &
                                  "WHERE CIA = '" & codCIA & "' AND COB_Activo = '1' " &
                                  "AND COB_Codigo LIKE '%" & codCobrador & "%'"

            Dim adapter As New SqlDataAdapter(query, coneccion)
            adapter.Fill(ds)
        Catch ex As SqlException
            MsgBox("La conexión no está disponible.")
        Catch e As Exception
            MsgBox(e.Message)
        Finally
            coneccion.Close()
        End Try

        Return ds
    End Function

    Function listarDepartamentos() As DataSet
        Dim conex As New Conexion
        Dim coneccion As New SqlConnection
        Dim dt As New DataTable
        Dim ds As New DataSet
        Try
            coneccion = conex.conectar()
            coneccion.Open()

            Dim query As String = "SELECT DEP_Codigo,DEP_Descripcion " &
                                  "FROM Departamento " &
                                  "WHERE DEP_Activo = '1'"

            Dim adapter As New SqlDataAdapter(query, coneccion)
            adapter.Fill(ds)

            dt = ds.Tables(0)
            Dim row As DataRow = dt.NewRow()
            row.Item("DEP_Codigo") = 0
            row.Item("DEP_Descripcion") = "Seleccionar..."
            dt.Rows.InsertAt(row, 0)

        Catch ex As SqlException
            MsgBox("La conexión no está disponible.")
        Catch e As Exception
            MsgBox(e.Message)
        Finally
            coneccion.Close()
        End Try

        Return ds
    End Function

    Function listarProvinciaXDepartamento(codDepartamento As String) As DataSet
        Dim conex As New Conexion
        Dim coneccion As New SqlConnection
        Dim dt As New DataTable
        Dim ds As New DataSet
        Try
            coneccion = conex.conectar()
            coneccion.Open()

            Dim query As String = "SELECT p.PRO_Codigo,p.PRO_Descripcion " &
                                  "FROM Departamento d " &
                                  "INNER JOIN Provincia p " &
                                  "ON p.DEP_Codigo = d.DEP_Codigo AND p.PRO_Activo = '1' " &
                                  "WHERE d.DEP_Codigo = '" & codDepartamento & "' AND d.DEP_Activo = '1'"

            Dim adapter As New SqlDataAdapter(query, coneccion)
            adapter.Fill(ds)

            dt = ds.Tables(0)
            Dim row As DataRow = dt.NewRow()
            row.Item("PRO_Codigo") = 0
            row.Item("PRO_Descripcion") = "Seleccionar..."
            dt.Rows.InsertAt(row, 0)

        Catch ex As SqlException
            MsgBox("La conexión no está disponible.")
        Catch e As Exception
            MsgBox(e.Message)
        Finally
            coneccion.Close()
        End Try

        Return ds
    End Function

    Function listarDistritosXProvincia(codProvincia As String) As DataSet
        Dim conex As New Conexion
        Dim coneccion As New SqlConnection
        Dim dt As New DataTable
        Dim ds As New DataSet
        Try
            coneccion = conex.conectar()
            coneccion.Open()

            Dim query As String = "SELECT d.DIS_Codigo, d.DIS_Descripcion " &
                                  "FROM Provincia p " &
                                  "INNER JOIN Distrito d " &
                                  "ON d.PRO_Codigo = p.PRO_Codigo AND d.DIS_Activo = '1' " &
                                  "WHERE p.PRO_Codigo = '" & codProvincia & "' AND p.PRO_Activo = '1'"

            Dim adapter As New SqlDataAdapter(query, coneccion)
            adapter.Fill(ds)

            dt = ds.Tables(0)
            Dim row As DataRow = dt.NewRow()
            row.Item("DIS_Codigo") = 0
            row.Item("DIS_Descripcion") = "Seleccionar..."
            dt.Rows.InsertAt(row, 0)

        Catch ex As SqlException
            MsgBox("La conexión no está disponible.")
        Catch e As Exception
            MsgBox(e.Message)
        Finally
            coneccion.Close()
        End Try

        Return ds
    End Function

    Function listarAlmacen(codAlmacen As String) As DataSet
        Dim conex As New Conexion
        Dim coneccion As New SqlConnection
        Dim ds As New DataSet
        Try
            coneccion = conex.conectar()
            coneccion.Open()

            Dim query As String = "SELECT ALM_Codigo,ALM_Descripcion " &
                                  "FROM Almacen " &
                                  "WHERE ALM_Vendible = '1' " &
                                  "AND ALM_Activo = '1' AND ALM_Codigo LIKE '" & codAlmacen & "%'"

            Dim adapter As New SqlDataAdapter(query, coneccion)
            adapter.Fill(ds)
        Catch ex As SqlException
            MsgBox("La conexión no está disponible.")
        Catch e As Exception
            MsgBox(e.Message)
        Finally
            coneccion.Close()
        End Try

        Return ds
    End Function

    Function listarProveedores(codProveedor As String) As DataSet
        Dim conex As New Conexion
        Dim coneccion As New SqlConnection
        Dim ds As New DataSet
        Try
            coneccion = conex.conectar()
            coneccion.Open()

            Dim query As String = "SELECT LTRIM(RTRIM(PROV_Codigo)) AS Codigo,LTRIM(RTRIM(PROV_DesCorta)) AS Descripcion " &
                                  "FROM Proveedor " &
                                  "WHERE PROV_Activo = '1' " &
                                  "AND PROV_Codigo LIKE '%" & codProveedor & "%'"

            Dim adapter As New SqlDataAdapter(query, coneccion)
            adapter.Fill(ds)
        Catch ex As SqlException
            MsgBox("La conexión no está disponible.")
        Catch e As Exception
            MsgBox(e.Message)
        Finally
            coneccion.Close()
        End Try

        Return ds
    End Function

    Function obtenerAgenciaXID(codAgencia As String)
        Dim conex As New Conexion
        Dim coneccion As New SqlConnection
        Dim ds As New DataSet
        Try
            coneccion = conex.conectar()
            coneccion.Open()

            Dim query As String = "SELECT AGE_Codigo, AGE_Ruc, AGE_Descripcion, AGE_Activo " &
                                  "FROM AgenciaTransporte " &
                                  "WHERE AGE_Codigo = '" & codAgencia & "'"

            Dim adapter As New SqlDataAdapter(query, coneccion)
            adapter.Fill(ds)
        Catch e As Exception
            ds = Nothing
        Finally
            coneccion.Close()
        End Try

        Return ds
    End Function

    Function obtenerAgencias()
        Dim conex As New Conexion
        Dim coneccion As New SqlConnection
        Dim ds As New DataSet
        Try
            coneccion = conex.conectar()
            coneccion.Open()

            Dim query As String = "SELECT AGE_Codigo, AGE_Descripcion " &
                                  "FROM AgenciaTransporte " &
                                  "WHERE AGE_Activo = '1'"

            Dim adapter As New SqlDataAdapter(query, coneccion)
            adapter.Fill(ds)
        Catch e As Exception
            ds = Nothing
        Finally
            coneccion.Close()
        End Try

        Return ds
    End Function

    Function obtenerAgenciaSucursalXCliente(codSucursal As String, codCliente As String) As DataSet
        Dim conex As New Conexion
        Dim coneccion As New SqlConnection
        Dim ds As New DataSet
        Try
            coneccion = conex.conectar()
            coneccion.Open()

            Dim query As String = "SELECT cs.ATS_Codigo, " &
                                  "at.AGE_Descripcion + ' - ' + ats.ATS_Descripcion AS Descripcion, " &
                                  "at.AGE_Codigo, at.AGE_Descripcion " &
                                  "FROM ClienteAgenciaTransporteSucursal cs " &
                                  "INNER JOIN AgenciaTransporteSucursal ats " &
                                  "ON ats.ATS_Codigo = cs.ATS_Codigo " &
                                  "INNER JOIN AgenciaTransporte at " &
                                  "ON at.AGE_Codigo = ats.AGE_Codigo " &
                                  "WHERE cs.CLI_Codigo = '" & codCliente & "' " &
                                  IIf(codSucursal = "", "", "AND cs.ATS_Codigo = '" & codSucursal & "'")

            Dim adapter As New SqlDataAdapter(query, coneccion)
            adapter.Fill(ds)
        Catch e As Exception
            ds = Nothing
        Finally
            coneccion.Close()
        End Try

        Return ds
    End Function

    Function listarAgencias(codSucursal As String, descripcion As String, codDept As String, codProv As String, codDis As String, tipo As Integer) As DataSet
        Dim conex As New Conexion
        Dim coneccion As New SqlConnection
        Dim ds As New DataSet
        Try
            coneccion = conex.conectar()
            coneccion.Open()

            Dim query As String = "SELECT ats.ATS_Codigo, at.AGE_Ruc, ats.ATS_Descripcion, ats.ATS_Direccion, " &
                                  "ats.ATS_Telefono, ats.ATS_Activo " &
                                  "FROM AgenciaTransporteSucursal ats " &
                                  "INNER JOIN AgenciaTransporte at " &
                                  "ON at.AGE_Codigo = ats.AGE_Codigo AND at.AGE_Activo = '1' " &
                                  "INNER JOIN Distrito d " &
                                  "ON d.DIS_Codigo = ats.DIS_Codigo " & IIf(codDis <> "0", " AND d.DIS_Codigo = '" & codDis & "'", "") & " AND d.DIS_Activo = '1' " &
                                  "INNER JOIN Provincia p " &
                                  "ON p.PRO_Codigo = d.PRO_Codigo " & IIf(codProv <> "0", " AND p.PRO_Codigo = '" & codProv & "'", "") & " AND p.PRO_Activo = '1' " &
                                  "INNER JOIN Departamento de " &
                                  "ON de.DEP_Codigo = p.DEP_Codigo " & IIf(codDept <> "0", " AND de.DEP_Codigo = '" & codDept & "'", "") & " AND de.DEP_Activo = '1' " &
                                  "WHERE ats.ATS_Activo = '1' "

            If codSucursal <> "" Then
                query = query & " AND ats.ATS_Codigo = '" & codSucursal & "' "
            End If

            If descripcion <> "" Then
                query = query & " AND ats.ATS_Descripcion LIKE '%" & descripcion & "%'"
            End If

            'query = query & "GROUP BY AGE_Codigo,AGE_Ruc,AGE_Descripcion,AGE_Direccion,AGE_Telefono,AGE_Activo"

            Dim adapter As New SqlDataAdapter(query, coneccion)
            adapter.Fill(ds)
        Catch e As Exception
            ds = Nothing
        Finally
            coneccion.Close()
        End Try

        Return ds
    End Function

    Function listarDireccionEntrega(clenCodigo As String, codCliente As String) As DataSet
        Dim conex As New Conexion
        Dim coneccion As New SqlConnection
        Dim ds As New DataSet
        Try
            coneccion = conex.conectar()
            coneccion.Open()

            Dim query As String = "SELECT CLEN_Codigo,CLEN_Direccion " &
                                  "FROM ClienteEntrega " &
                                  "WHERE CLI_Codigo = '" & codCliente & "' " &
                                  "AND CLEN_Codigo LIKE '%" & clenCodigo & "%' " &
                                  "AND CLEN_Activo = '1'"

            Dim adapter As New SqlDataAdapter(query, coneccion)
            adapter.Fill(ds)
        Catch ex As SqlException
            MsgBox("La conexión no está disponible.")
        Catch e As Exception
            MsgBox(e.Message)
        Finally
            coneccion.Close()
        End Try

        Return ds
    End Function

    Function obtenerUltimosPedidos(codCliente As String) As DataSet
        Dim conex As New Conexion
        Dim coneccion As New SqlConnection
        Dim ds As New DataSet
        Try
            coneccion = conex.conectar()
            coneccion.Open()

            Dim query As String = "SELECT dc.DCI_Codigo, dc.DCI_TotalCobrar, e.EST_Descripcion " &
                                  "FROM Documento_Cliente dc " &
                                  "INNER JOIN Estado e " &
                                  "ON e.EST_Codigo = dc.DCI_Estado AND e.EST_Activo = '1' " &
                                  "WHERE dc.CLI_Codigo = '" & codCliente & "' " &
                                  "AND dc.TDOC_Codigo = 'P'"

            Dim adapter As New SqlDataAdapter(query, coneccion)
            adapter.Fill(ds)
        Catch ex As SqlException
            MsgBox("La conexión no está disponible.")
        Catch e As Exception
            MsgBox(e.Message)
        Finally
            coneccion.Close()
        End Try

        Return ds
    End Function

    Function obtenerDetallePedido(codPedido As String) As DataSet
        Dim conex As New Conexion
        Dim coneccion As New SqlConnection
        Dim ds As New DataSet
        Try
            coneccion = conex.conectar()
            coneccion.Open()

            Dim query As String = "SELECT dcd.DCD_Codigo, a.ART_Descripcion, '00' AS Almacen, um.UNM_Abreviatura, " &
                                  "ISNULL(dcd.DCD_ValorUnit,'0.00') AS Precio, " &
                                  "dcd.DCD_Ordenado, dcd.DCD_Atendido, " &
                                  "ISNULL(dcd.DCD_ValorUnit,0.00) * dcd.DCD_Atendido - ISNULL(dcd.DCD_Descuento,0.00)  AS Subtotal " &
                                  "FROM Documento_Cliente_Detalle dcd " &
                                  "INNER JOIN Articulo a " &
                                  "ON a.ART_Codigo = dcd.ART_Codigo AND a.ART_Activo = '1' " &
                                  "INNER JOIN UnidadMedida um " &
                                  "ON um.UNM_Codigo = a.UNM_Codigo AND um.UNM_Activo = '1' " &
                                  "WHERE dcd.DCI_Codigo = '" & codPedido & "' " &
                                  "AND dcd.DCD_Activo = '1'"

            Dim adapter As New SqlDataAdapter(query, coneccion)
            adapter.Fill(ds)
        Catch ex As SqlException
            MsgBox("La conexión no está disponible.")
        Catch e As Exception
            MsgBox(e.Message)
        Finally
            coneccion.Close()
        End Try

        Return ds
    End Function

    Function obtenerGuiaFactura(codPedido As String) As String
        Dim texto As String = ""
        Dim sqlComm As OdbcDataAdapter
        Dim sConn As String = ""
        Dim sConn1 As String = ""
        Dim mesanio As String = ""
        Dim mes As String = ""
        Dim anio As String = ""
        Dim dt As New DataSet

        Dim query1 As String = "SELECT meses,anhos FROM matrocfg where codcia = '01'"
        sConn = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" & System.IO.Path.GetDirectoryName(Me.Path1 + "MATROCFG.DBF") & ";"

        Using dbConn As New System.Data.Odbc.OdbcConnection(sConn)
            dbConn.Open()
            Dim ds As New DataSet
            sqlComm = New OdbcDataAdapter(query1, dbConn)
            sqlComm.Fill(ds)
            mes = ds.Tables(0).Rows(0)("meses").ToString.Trim
            anio = ds.Tables(0).Rows(0)("anhos").ToString.Trim
            mesanio = anio.Substring(2) & mes
            dbConn.Close()
        End Using

        Dim query2 As String = "SELECT RTRIM(DOCTO) + ' / ' + RTRIM(REFER) AS GuiaFactura FROM ARINGDO1 WHERE DOCTO2 = '" & codPedido & "'"
        sConn1 = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" & System.IO.Path.GetDirectoryName(Me.Path2 + "1403" + "\ARINGDO1.DBF") & ";"

        Using dbConn1 As New System.Data.Odbc.OdbcConnection(sConn1)
            dbConn1.Open()
            sqlComm = New OdbcDataAdapter(query2, dbConn1)
            sqlComm.Fill(dt)
            dbConn1.Close()
        End Using

        If dt.Tables(0).Rows.Count <> 0 Then
            texto = dt.Tables(0).Rows(0)("GuiaFactura").ToString.Trim
        End If

        Return texto
    End Function

    Function obtenerDiarioDistribucion(codTurno As String, fecha As String, codVehiculo As String) As DataSet
        Dim diario As New DataTable
        diario.Columns.Add("clienteAgencia")
        diario.Columns.Add("direccion")
        diario.Columns.Add("guiaFactura")
        diario.Columns.Add("bultosEnvases")
        diario.Columns.Add("condVenta")
        diario.Columns.Add("monedaAbrev")
        diario.Columns.Add("totalCobrar")
        diario.Columns.Add("pedido")

        Dim conex As New Conexion
        Dim coneccion As New SqlConnection
        Dim ds1 As New DataSet
        Try
            coneccion = conex.conectar()
            coneccion.Open()

            '  "(CASE WHEN dc.AGE_Codigo IS NOT NULL THEN at.AGE_Codigo "
            ' "ELSE c.CLI_Codigo END) AS Codigo, " &
            Dim query As String = "SELECT dc.DCI_Codigo,(CASE WHEN dc.AGE_Codigo IS NOT NULL THEN at.AGE_Descripcion " &
                                  "ELSE c.CLI_Descripcion END) AS ClienteAgencia, " &
                                  "(CASE WHEN dc.AGE_Codigo IS NOT NULL THEN RTRIM(ats.ATS_Direccion) + ' / ' + " &
                                  "dbo.FNX_ObtenerAgrupado(LTrim(RTrim(d2.DIS_Descripcion)), LTrim(RTrim(p2.PRO_Descripcion)), LTrim(RTrim(de2.DEP_Descripcion))) " &
                                  "ELSE RTRIM(ce.CLEN_Direccion) + ' / ' + " &
                                  "dbo.FNX_ObtenerAgrupado(LTRIM(RTRIM(d1.DIS_Descripcion)),LTRIM(RTRIM(p1.PRO_Descripcion)),LTRIM(RTRIM(de1.DEP_Descripcion))) END) AS Direccion, " &
                                  "(CASE WHEN tabla.Cantidad = 1 THEN CAST(tabla.Cantidad AS VARCHAR(10)) + ' Bulto' " &
                                  "ELSE CAST(tabla.Cantidad AS VARCHAR(10)) + ' Bultos' END) " &
                                  "+ ' / ' +  " &
                                  "(CASE WHEN dbo.FNX_SumarLargos(dc.CIA,dc.SUC_Codigo,dc.TDOC_Codigo,dc.DCI_Codigo) = 1 THEN CAST(dbo.FNX_SumarLargos(dc.CIA,dc.SUC_Codigo,dc.TDOC_Codigo,dc.DCI_Codigo) AS VARCHAR(10)) + ' Largo' " &
                                  "ELSE CAST(dbo.FNX_SumarLargos(dc.CIA,dc.SUC_Codigo,dc.TDOC_Codigo,dc.DCI_Codigo) AS VARCHAR(10)) + ' Largos' END) AS BultosEnvases, " &
                                  "cv.CNV_Descripcion, m.MON_Abreviatura, dc.DCI_TotalCobrar " &
                                  "FROM Programacion p " &
                                  "INNER JOIN Documento_Cliente dc " &
                                  "ON dc.DCI_Codigo = p.DCI_Codigo AND dc.DCI_Programado = '1' " &
                                  "LEFT JOIN AgenciaTransporte at " &
                                  "ON at.AGE_Codigo = dc.AGE_Codigo AND at.AGE_Activo = '1' " &
                                  "LEFT JOIN AgenciaTransporteSucursal ats " &
                                  "ON ats.ATS_Codigo = dc.ATS_Codigo AND ats.ATS_Activo = '1' " &
                                  "INNER JOIN Cliente c " &
                                  "ON c.CLI_Codigo = dc.CLI_Codigo AND c.CLI_Activo = '1' " &
                                  "INNER JOIN ClienteEntrega ce " &
                                  "ON ce.CLEN_Codigo = dc.CLEN_Codigo AND ce.CLI_Codigo = dc.CLI_Codigo " &
                                  "AND ce.CLEN_Activo = '1' " &
                                  "INNER JOIN Documento_Cliente_Detalle dcd " &
                                  "ON dcd.TDOC_Codigo = dc.TDOC_Codigo AND dcd.DCI_Codigo = dc.DCI_Codigo AND dcd.DCD_Activo = '1' " &
                                  "INNER JOIN Documento_Cliente_Almacen_Detalle dcad " &
                                  "ON dcad.TDOC_Cliente = dcd.TDOC_Codigo AND dcad.DCI_Codigo = dcd.DCI_Codigo " &
                                  "AND dcad.DCD_Codigo = dcd.DCD_Codigo AND dcad.DCAD_Activo = '1' " &
                                  "INNER JOIN Documento_Almacen_Detalle dad " &
                                  "ON dad.TDOC_Codigo = dcad.TDOC_Almacen AND dad.DCA_Codigo = dcad.DCA_Codigo " &
                                  "AND dcad.DAD_Codigo = dad.DAD_Codigo AND dad.DAD_Activo = '1' " &
                                  "LEFT JOIN Articulo a " &
                                  "ON a.ART_Codigo = dad.ART_Codigo AND dad.DAD_Cantidad%a.ART_CantLargo > 0 AND a.ART_Activo = '1' " &
                                  "INNER JOIN Documento_Cliente_Bulto dcb " &
                                  "ON dcb.DCI_Codigo = dc.DCI_Codigo AND dcb.DCB_Activo = '1' " &
                                  "LEFT JOIN (SELECT dc.DCI_Codigo, SUM(dcb.DCB_Cantidad) AS Cantidad " &
                                  "FROM Documento_Cliente dc " &
                                  "INNER JOIN Documento_Cliente_Bulto dcb " &
                                  "ON dcb.DCI_Codigo = dc.DCI_Codigo AND dcb.DCB_Activo = '1' " &
                                  "GROUP BY dc.DCI_Codigo) AS tabla " &
                                  "ON tabla.DCI_Codigo = dc.DCI_Codigo " &
                                  "INNER JOIN CondicionVenta cv " &
                                  "ON cv.CNV_Codigo = dc.CNV_Codigo AND cv.CNV_Activo = '1' " &
                                  "INNER JOIN Moneda m " &
                                  "ON m.MON_Codigo = dc.MON_Codigo AND m.MON_Activo = '1' " &
                                  "INNER JOIN Distrito d1 " &
                                  "ON d1.DIS_Codigo = ce.DIS_Codigo AND d1.DIS_Activo = '1' " &
                                  "INNER JOIN Provincia p1 " &
                                  "ON p1.PRO_Codigo = d1.PRO_Codigo AND p1.PRO_Activo = '1' " &
                                  "INNER JOIN Departamento de1 " &
                                  "ON de1.DEP_Codigo = p1.DEP_Codigo AND de1.DEP_Activo = '1' " &
                                  "LEFT JOIN Distrito d2 " &
                                  "ON d2.DIS_Codigo = ats.DIS_Codigo AND d2.DIS_Activo = '1' " &
                                  "LEFT JOIN Provincia p2 " &
                                  "ON p2.PRO_Codigo = d2.PRO_Codigo AND p2.PRO_Activo = '1' " &
                                  "LEFT JOIN Departamento de2 " &
                                  "ON de2.DEP_Codigo = p2.DEP_Codigo AND de2.DEP_Activo = '1' " &
                                  "WHERE p.PRO_Activo = '1' AND p.TUR_Codigo = @TUR " &
                                  "AND p.PRO_Fecha = @FEC AND p.VEH_Codigo = @VEH " &
                                  "GROUP BY dc.DCI_Codigo,dc.AGE_Codigo,c.CLI_Descripcion,at.AGE_Descripcion,ats.ATS_Direccion,ce.CLEN_Direccion,tabla.Cantidad, " &
                                  "c.CLI_Codigo,cv.CNV_Descripcion,m.MON_Abreviatura,at.AGE_Codigo,dc.DCI_TotalCobrar, " &
                                  "d2.DIS_Descripcion,p2.PRO_Descripcion,de2.DEP_Descripcion,p.PRO_Orden, " &
                                  "d1.DIS_Descripcion,p1.PRO_Descripcion,de1.DEP_Descripcion,ce.CLEN_Direccion, " &
                                  "dc.CIA,dc.SUC_Codigo,dc.TDOC_Codigo " &
                                  "ORDER BY p.PRO_Orden"

            Dim comando As New SqlCommand(query, coneccion)
            With comando
                .Parameters.AddWithValue("@TUR", codTurno)
                .Parameters.AddWithValue("@FEC", fecha)
                .Parameters.AddWithValue("@VEH", codVehiculo)
            End With
            Dim adapter As New SqlDataAdapter(comando)
            adapter.Fill(ds1)
        Catch ex As SqlException
            MsgBox("La conexión no está disponible.")
        Catch e As Exception
            MsgBox(e.Message)
        Finally
            coneccion.Close()
        End Try

        If ds1.Tables(0).Rows.Count <> 0 Then
            For i = 0 To ds1.Tables(0).Rows.Count - 1
                Dim dr As DataRow = diario.NewRow
                dr("clienteAgencia") = ds1.Tables(0).Rows(i)("ClienteAgencia").ToString.Trim
                dr("direccion") = ds1.Tables(0).Rows(i)("Direccion").ToString.Trim
                dr("guiaFactura") = obtenerGuiaFactura(ds1.Tables(0).Rows(i)("DCI_Codigo").ToString.Trim)
                dr("bultosEnvases") = ds1.Tables(0).Rows(i)("BultosEnvases").ToString.Trim
                dr("condVenta") = ds1.Tables(0).Rows(i)("CNV_Descripcion").ToString.Trim
                dr("monedaAbrev") = ds1.Tables(0).Rows(i)("MON_Abreviatura").ToString.Trim
                dr("totalCobrar") = ds1.Tables(0).Rows(i)("DCI_TotalCobrar").ToString.Trim
                dr("pedido") = ds1.Tables(0).Rows(i)("DCI_Codigo").ToString.Trim

                diario.Rows.Add(dr)
                diario.AcceptChanges()
            Next
        End If

        Dim ds2 As New DataSet
        ds2.Tables.Add(diario)

        Return ds2
    End Function

    Function obtenerPorcentajePercepcion(artPercepcion As String, artZEmergencia As String, cliRetencion As String,
                                         cliPercepcion As String, distrito As String) As DataSet
        Dim conex As New Conexion
        Dim coneccion As New SqlConnection
        Dim ds As New DataSet
        Try
            coneccion = conex.conectar()
            coneccion.Open()

            Dim query As String = "SELECT PER_Codigo, CAST(PERC_Porcentaje * 100 AS VARCHAR(10)) + ' %' AS Porcentaje, " &
                                  "PERC_Porcentaje " &
                                  "FROM Percepcion " &
                                  "WHERE ART_Percecion = @ARTP AND ART_ZEmergencia = @ARTZE " &
                                  "AND CLI_Retencion = @CRET AND CLI_Percepcion = @CPER " &
                                  "AND CLI_ZEmergencia = (SELECT DIS_ZEmergencia FROM Distrito WHERE DIS_Codigo = @DIS AND DIS_Activo = '1')"

            Dim comando As New SqlCommand(query, coneccion)
            With comando
                .Parameters.AddWithValue("@ARTP", artPercepcion)
                .Parameters.AddWithValue("@ARTZE", artZEmergencia)
                .Parameters.AddWithValue("@CRET", cliRetencion)
                .Parameters.AddWithValue("@CPER", cliPercepcion)
                .Parameters.AddWithValue("@DIS", distrito)
            End With
            Dim adapter As New SqlDataAdapter(comando)
            adapter.Fill(ds)
        Catch ex As SqlException
            MsgBox("La conexión no está disponible.")
        Catch e As Exception
            MsgBox(e.Message)
        Finally
            coneccion.Close()
        End Try

        Return ds
    End Function

    Function obtenerPedidosXFiltro(codEstado As String, cliente As String, tdoc As String, fechaI As String, fechaF As String, vendedor As String) As DataSet
        Dim conex As New Conexion
        Dim coneccion As New SqlConnection
        Dim ds As New DataSet
        Dim query As String = ""
        Try
            coneccion = conex.conectar()
            coneccion.Open()

            If codEstado <> "" Then
                query = "SELECT DCI_Codigo, CONVERT(VARCHAR(15),DCI_Fecha,103) AS Fecha, " &
                        "c.CLI_Descripcion, v.VEN_DesCorta, m.MON_Abreviatura, td.TDO_Abreviatura, " &
                        "cv.CNV_Descripcion, fp.FOP_Descripcion " &
                        "FROM Documento_Cliente dc " &
                        "INNER JOIN Cliente c " &
                        "ON c.CLI_Codigo = dc.CLI_Codigo AND c.CLI_Activo = '1' " &
                        "INNER JOIN Moneda m " &
                        "ON m.MON_Codigo = dc.MON_Codigo AND m.MON_Activo = '1' " &
                        "INNER JOIN TipoDocumento td " &
                        "ON td.TDO_Codigo = dc.TDO_Codigo AND td.TDO_Activo = '1' " &
                        "INNER JOIN CondicionVenta cv " &
                        "ON cv.CNV_Codigo = dc.CNV_Codigo AND cv.CNV_Activo = '1' " &
                        "INNER JOIN FormaPago fp " &
                        "ON fp.FOP_Codigo = dc.FOP_Codigo AND fp.FOP_Activo = '1' " &
                        "INNER JOIN Vendedor v " &
                        "ON v.VEN_Codigo = dc.VEN_Codigo AND v.VEN_Activo = '1' " &
                        "WHERE dc.DCI_Estado = '" & codEstado & "' "
            Else
                query = "SELECT DCI_Codigo, CONVERT(VARCHAR(15),DCI_Fecha,103) AS Fecha, " &
                        "c.CLI_Descripcion, v.VEN_DesCorta, m.MON_Abreviatura, td.TDO_Abreviatura, " &
                        "cv.CNV_Descripcion, fp.FOP_Descripcion " &
                        "FROM Documento_Cliente dc " &
                        "INNER JOIN Cliente c " &
                        "ON c.CLI_Codigo = dc.CLI_Codigo AND c.CLI_Activo = '1' " &
                        "INNER JOIN Moneda m " &
                        "ON m.MON_Codigo = dc.MON_Codigo AND m.MON_Activo = '1' " &
                        "INNER JOIN TipoDocumento td " &
                        "ON td.TDO_Codigo = dc.TDO_Codigo AND td.TDO_Activo = '1' " &
                        "INNER JOIN CondicionVenta cv " &
                        "ON cv.CNV_Codigo = dc.CNV_Codigo AND cv.CNV_Activo = '1' " &
                        "LEFT JOIN FormaPago fp " &
                        "ON fp.FOP_Codigo = dc.FOP_Codigo AND fp.FOP_Activo = '1' " &
                        "LEFT JOIN Vendedor v " &
                        "ON v.VEN_Codigo = dc.VEN_Codigo AND v.VEN_Activo = '1' " &
                        "WHERE dc.CLI_Codigo LIKE '" & cliente & "%' " &
                        "AND dc.TDOC_Codigo = '" & tdoc & "' "

                If fechaI <> "" Then
                    query = query & " AND CONVERT(VARCHAR(15),dc.DCI_Fecha,103) BETWEEN '" & fechaI & "' AND '" & fechaF & "' "
                End If
                If vendedor <> "" Then
                    query = query & " AND dc.VEN_Codigo = '" & vendedor & "'"
                End If
            End If

            Dim adapter As New SqlDataAdapter(query, coneccion)
            adapter.Fill(ds)
        Catch ex As SqlException
            MsgBox("La conexión no está disponible.")
        Catch e As Exception
            MsgBox(e.Message)
        Finally
            coneccion.Close()
        End Try

        Return ds
    End Function

    Function listarLineas() As DataSet
        Dim conex As New Conexion
        Dim coneccion As New SqlConnection
        Dim dt As New DataTable
        Dim ds As New DataSet
        Try
            coneccion = conex.conectar()
            coneccion.Open()

            Dim query As String = "SELECT l.LIN_Codigo, l.LIN_Descripcion " &
                                  "FROM Consultas..Linea_Familia lf " &
                                  "INNER JOIN Consultas..Linea l " &
                                  "ON l.LIN_Codigo = lf.LIN_Codigo AND l.LIN_Activo = '1' " &
                                  "WHERE lf.LIF_Activo = '1' " &
                                  "GROUP BY l.LIN_Codigo, l.LIN_Descripcion"

            Dim adapter As New SqlDataAdapter(query, coneccion)
            adapter.Fill(ds)

            dt = ds.Tables(0)
            Dim row As DataRow = dt.NewRow()
            row.Item("LIN_Codigo") = 0
            row.Item("LIN_Descripcion") = "Seleccionar..."
            dt.Rows.InsertAt(row, 0)

        Catch ex As SqlException
            MsgBox("La conexión no está disponible.")
        Catch e As Exception
            MsgBox(e.Message)
        Finally
            coneccion.Close()
        End Try

        Return ds
    End Function

    Function listarFamiliasXLinea(codLinea As String) As DataSet
        Dim conex As New Conexion
        Dim coneccion As New SqlConnection
        Dim dt As New DataTable
        Dim ds As New DataSet
        Try
            coneccion = conex.conectar()
            coneccion.Open()

            Dim query As String = "SELECT f.FAM_Codigo, f.FAM_Descripcion " &
                                  "FROM Consultas..Linea_Familia lf " &
                                  "INNER JOIN Consultas..Familia f " &
                                  "ON f.FAM_Codigo = lf.FAM_Codigo AND f.FAM_Activo = '1' " &
                                  "WHERE lf.LIF_Activo = '1' AND lf.LIN_Codigo = '" & codLinea & "'"

            Dim adapter As New SqlDataAdapter(query, coneccion)
            adapter.Fill(ds)

            dt = ds.Tables(0)
            Dim row As DataRow = dt.NewRow()
            row.Item("FAM_Codigo") = 0
            row.Item("FAM_Descripcion") = "Seleccionar..."
            dt.Rows.InsertAt(row, 0)

        Catch ex As SqlException
            MsgBox("La conexión no está disponible.")
        Catch e As Exception
            MsgBox(e.Message)
        Finally
            coneccion.Close()
        End Try

        Return ds
    End Function

    Function listarSubFamiliasXLineaXFamilia(codLinea As String, codFamilia As String) As DataSet
        Dim conex As New Conexion
        Dim coneccion As New SqlConnection
        Dim dt As New DataTable
        Dim ds As New DataSet
        Try
            coneccion = conex.conectar()
            coneccion.Open()

            Dim query As String = "SELECT sf.SFM_Codigo, sf.SFM_Descripcion " &
                                  "FROM Consultas..LIF_SubFamilia lfs " &
                                  "INNER JOIN Consultas..SubFamilia sf " &
                                  "ON sf.SFM_Codigo = lfs.SFM_Codigo AND sf.SFM_Activo = '1' " &
                                  "WHERE lfs.LFS_Activo = '1' AND lfs.LIN_Codigo = '" & codLinea & "' AND lfs.FAM_Codigo = '" & codFamilia & "'"

            Dim adapter As New SqlDataAdapter(query, coneccion)
            adapter.Fill(ds)

            dt = ds.Tables(0)
            Dim row As DataRow = dt.NewRow()
            row.Item("SFM_Codigo") = 0
            row.Item("SFM_Descripcion") = "Seleccionar..."
            dt.Rows.InsertAt(row, 0)

        Catch ex As SqlException
            MsgBox("La conexión no está disponible.")
        Catch e As Exception
            MsgBox(e.Message)
        Finally
            coneccion.Close()
        End Try

        Return ds
    End Function

    Function obtenerMotivosDesaprobacion() As DataSet
        Dim conex As New Conexion
        Dim coneccion As New SqlConnection
        Dim dt As New DataTable
        Dim ds As New DataSet
        Try
            coneccion = conex.conectar()
            coneccion.Open()

            Dim query As String = "SELECT item, descrip " &
                                  "FROM OPENQUERY(GULDASYSBD, " &
                                  "'SELECT item,descrip FROM tablas/INFOGEN WHERE code+subcode = ''MD01''')"

            Dim adapter As New SqlDataAdapter(query, coneccion)
            adapter.Fill(ds)

            dt = ds.Tables(0)
            Dim row As DataRow = dt.NewRow()
            row.Item("item") = 0
            row.Item("descrip") = "Seleccionar..."
            dt.Rows.InsertAt(row, 0)

        Catch ex As SqlException
            MsgBox("La conexión no está disponible.")
        Catch e As Exception
            MsgBox(e.Message)
        Finally
            coneccion.Close()
        End Try

        Return ds
    End Function

End Class
